const fs = require('fs');
const Mysql = require('mysql-pro');

const db = new Mysql({
    mysql: {
        host: 'localhost',
        port: 3306,
        user: 'root',
        password: 'root',
        database: 'zhihu'
    }
})

const arr = JSON.parse(fs.readFileSync('./topics.txt').toString());

let topics = {}, topic_ID = 1;
let authors = {}, author_ID = 1;
let questions = {}, question_ID = 1;
let answers = {}, answer_ID = 1;

arr.forEach(question => {
    // topics相关，遍历每项question下的所有topices标签
    // 先给topics存储 { '标签名': topic_ID }，然后给原始数据增加topics属性，对应这个question项含有的topic_ID。
    question.topics = question.topices.map((json) => {
        let {title} = json;
        title = title.replace(/\n|\t/g, '');
        if (!topics[title]) {
            topics[title] = topic_ID++;
        }
        return topics[title];
    }).join(',');

    // authors相关，遍历每项question下的bestAnswer的author和answers下的author
    // 先把所有的author信息保存到authors中，增加一个id属性，将question的author对象删除，只留下一个对应authors数据中的id值就行
    [question.bestAnswer.author, ...question.answers.map((answer) => { return answer.author })].forEach((author, index) => {

        let old_id = author.id;
        if (!authors[old_id]) {
            authors[author.id] = author;
            author.id = author_ID++;
        }

        if (index == 0) {
            delete question.bestAnswer.author;
            question.bestAnswer.author_ID = authors[old_id].id
        } else {
            delete question.answers[index - 1].author;
            question.answers[index - 1].author_ID = authors[old_id].id;
        }
        return authors[old_id];
    })

    // questions 相关，直接把每项question复制给questions对象 { question_ID: question }。
    let ID = question_ID;
    questions[question_ID++] = question;

    // answer 相关，遍历question的bestAnswer和answers，
    // 把所有answer信息保存到answers中 { answer_ID: answer }，给每项question的answer中添加question_ID 和 answer_ID
    [question.bestAnswer, ...question.answers].forEach((answer) => {
        answer.id = answer_ID;
        answer.question_ID = ID;
        answers[answer_ID++] = answer;
    })

});

(async () => {
    function dataJoin(...args) {
        return "('" + args.map((item) => {
            item = item || '';
            item = item.toString().replace(/'/g, '\\\'');
            return item;
        }).join("','") + "')"
    }

    // 插入topics
    let aTopics = [];
    for (let title in topics) {
        let ID = topics[title];
        aTopics.push(dataJoin(ID, title));
    }
    let topic_sql = `INSERT INTO topic_table VALUE${aTopics.join(',')}`;

    // 插入authors
    let aAuthors = [];
    for (let ID in authors) {
        let author = authors[ID];
        aAuthors.push(dataJoin(author.id, author.type, author.name, author.gender, author.userType, author.avatarUrl, author.headline, author.followerCount));
    }
    let author_sql = `INSERT INTO author_table VALUE${aAuthors.join(',')}`;

    // 插入questions
    let aQuestions = [];
    for (let ID in questions) {
        let question = questions[ID];
        aQuestions.push(dataJoin(ID, question.title, question.question_content, question.topics, question.attention_count, question.view_count, question.bestAnswer.id));
    }
    let question_sql = `INSERT INTO question_table VALUE${aQuestions.join(',')}`;

    // 插入answers
    let aAnswers = [];
    for (let ID in answers) {
        let answer = answers[ID];
        aAnswers.push(dataJoin(ID, answer.author_ID, answer.question_ID, answer.content, answer.createdTime));
    }
    let answer_sql = `INSERT INTO answer_table VALUE${aAnswers.join(',')}`;

    await db.startTransaction();
    // await db.executeTransaction(topic_sql);
    // await db.executeTransaction(author_sql);
    // await db.executeTransaction(question_sql);
    await db.executeTransaction(answer_sql);
    await db.stopTransaction();


})()
